with tab as (
 select billcode,
       scansitecode,
       split(concat(scantime,'\001',
       coalesce(inputtime,''),'\001',
       coalesce(nextstationcode,''),'\001',
       coalesce(operatedate,''),'\001',
       coalesce(packagecode,''),'\001',
       coalesce(returnflag,''),'\001',
       coalesce(scanuser,''),'\001',
       coalesce(scanusercode,''),'\001',
       coalesce(uploadtime,''),'\001',
       coalesce(weight,''),'\001',
       coalesce(scansitecode,''),'\001',
       coalesce(sendcustomer,'')
       ),'\001') as wid_columns
from jms_ods.tab_barscan_taking      --21天内有效
where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24) AND '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dwd.dwd_barscan_taking_dt
select
billcode                                                 as billcode,
scansitecode                                             as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as nextstationcode_later,
max(wid_columns)[3]                                      as operatedate_later,
max(wid_columns)[4]                                      as packagecode_later ,
max(wid_columns)[5]                                      as returnflag_later,
max(wid_columns)[6]                                      as scanuser_later,
max(wid_columns)[7]                                      as scanusercode_later,
max(wid_columns)[8]                                      as uploadtime_later,
cast(max(wid_columns)[9] AS DECIMAL(10, 2))              as weight_later,

scansitecode                                             as scansitecode_early,
min(wid_columns)[0]                                     as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as nextstationcode_early,
min(wid_columns)[3]                                      as operatedate_early,
min(wid_columns)[4]                                      as packagecode_early,
min(wid_columns)[5]                                      as returnflag_early,
min(wid_columns)[6]                                      as scanuser_early,
min(wid_columns)[7]                                      as scanusercode_early,
min(wid_columns)[8]                                      as uploadtime_early,
cast(min(wid_columns)[9] AS DECIMAL(10, 2))              as weight_early,

max(wid_columns)[7]                                      as sendcustomer,     -- new add

'bill_scansite'                                          as dimtype,
date(max(wid_columns)[0])                                as dt
from tab
 group by billcode,scansitecode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}'

union all

select
billcode                                                 as billcode,
max(wid_columns)[10]                                     as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as nextstationcode_later,
max(wid_columns)[3]                                      as operatedate_later,
max(wid_columns)[4]                                      as packagecode_later ,
max(wid_columns)[5]                                      as returnflag_later,
max(wid_columns)[6]                                      as scanuser_later,
max(wid_columns)[7]                                      as scanusercode_later,
max(wid_columns)[8]                                      as uploadtime_later,
cast(max(wid_columns)[9] AS DECIMAL(10, 2))              as weight_later,

min(wid_columns)[10]                                     as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as nextstationcode_early,
min(wid_columns)[3]                                      as operatedate_early,
min(wid_columns)[4]                                      as packagecode_early,
min(wid_columns)[5]                                      as returnflag_early,
min(wid_columns)[6]                                      as scanuser_early,
min(wid_columns)[7]                                      as scanusercode_early,
min(wid_columns)[8]                                      as uploadtime_early,
cast(min(wid_columns)[9] AS DECIMAL(10, 2))              as weight_early,

max(wid_columns)[7]                                      as sendcustomer,     -- new add

'billcode'                                               as dimtype,
date(max(wid_columns)[0])                                as dt

from tab
 group by billcode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}'
    distribute by dt ,dimtype, pmod(hash(rand()),8);

